package com.springboot.fastcode.utils;


import com.springboot.fastcode.common.entity.FieldInfo;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库工具
 *    
 * @author liaofuxing
 * @E-mail liaofuxing@outlook.com
 * @date 2020/04/09 19:56
 */
public class DBUtils {

    private final DataSource dataSource;

    public DBUtils(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 获取数据库中所有的表名称
     *
     * @return tableList 该数据库中所有的表名称
     */
    public List<String> getTableList() throws SQLException {
        ResultSet rs = null;
        Connection conn = null;
        List<String> tables = new ArrayList<>();
        try {
            conn = dataSource.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();
            rs = metaData.getTables(conn.getCatalog(), "%", null, new String[]{"TABLE"});
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tables.add(tableName);
            }
            return tables;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            assert rs != null;
            rs.close();
            conn.close();
        }
        return tables;
    }


    /**
     *
     * 获取表中字段的所有注释
     *  @param tableName 表名
     *  @return List<String> 注释集合
     *    
     */
    public List<String> getColumnRemakes(String tableName) {
        String SQL = "SELECT * FROM ";
        //与数据库的连接
        Connection conn;
        PreparedStatement ps;
        String tableSql = SQL + tableName;
        // 列注释集合
        List<String> columnCommentRemakes = new ArrayList<>();
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(tableSql);
            rs = ps.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnCommentRemakes.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.getErrorCode();
                }
            }
        }
        return columnCommentRemakes;
    }


    /**
     * 获取指定表的所有字段名称
     *
     * @param tableName 表名称
     * @return 该表所有的字段名称
     */
    public List<FieldInfo> getDBFieldList(String tableName) throws SQLException {
        ResultSet rs = null;
        Connection conn = null;
        String SQL = "select * from ";
        List<FieldInfo> fieldList = new ArrayList<>();
        try {
            conn = dataSource.getConnection();
            PreparedStatement cs = conn.prepareStatement(SQL + tableName);
            rs = cs.executeQuery(SQL + tableName);
            ResultSetMetaData data = rs.getMetaData();

            for (int i = 1; i <= data.getColumnCount(); i++) {
                String columnName = data.getColumnName(i);
                //获得指定列的数据类型名
                String columnTypeName = data.getColumnTypeName(i);

                FieldInfo fieldInfo = new FieldInfo(columnName, columnTypeName);
                fieldList.add(fieldInfo);
            }
            List<String> columnRemakesList = getColumnRemakes(tableName);
            for (int i = 0; i < fieldList.size(); i++) {
                FieldInfo fieldInfo = fieldList.get(i);
                fieldInfo.setRemakes(columnRemakesList.get(i));
            }
            return fieldList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            assert rs != null;
            rs.close();
            conn.close();
        }
        return fieldList;
    }


//    /**
//     *
//     * 获取指定表的所有字段名称，不包含注释
//     *
//     * @param tableName 表名称
//     * @return 该表所有的字段名称
//     */
//    public List<FieldInfo> getDBFieldList(String tableName) throws SQLException {
//        Connection conn = null;
//        List<FieldInfo> fieldList = new ArrayList<>();
//        try {
//            conn = dataSource.getConnection();
//            DatabaseMetaData meta = conn.getMetaData();
//
//            ResultSet rs = meta.getColumns(null, "%", tableName, "%");
//
//            while (rs.next()) {
//
//                String columnName = rs.getString("COLUMN_NAME");
//                //获得指定列的数据类型名
//                String columnTypeName = rs.getString("TYPE_NAME");
//                String remakes = rs.getString("REMARKS");
//                FieldInfo fieldInfo = new FieldInfo(columnName, columnTypeName, remakes);
//                fieldList.add(fieldInfo);
//
//            }
//
//
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            assert conn != null;
//            conn.close();
//        }
//
//        return fieldList;
//    }

}
